package com.dbsync.core;

import com.dbsync.exception.DbsyncException;
import com.dbsync.exception.DbsyncUncaughtExceptionHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.TimeUnit;

/**
 * SQL执行类
 */
public class SQLExecutor {
    // 日志
    private final static Logger logger = LoggerFactory.getLogger(SQLExecutor.class);
    // 数据库工具对象
    private DBUtil dbUtil;
    // 任务索引
    private int targetIndex;

    public SQLExecutor(DBUtil dbUtil, int targetIndex) {
        this.dbUtil = dbUtil;
        this.targetIndex = targetIndex;
    }

    /**
     * 获取任务索引
     * @return 返回任务索引
     */
    public int getTargetIndex(){
        return this.targetIndex;
    }

    /**
     * 执行同步任务
     */
    public void excute() throws Exception{
        try{
            this.dbUtil.init();
            logger.info("*************************************同步操作任务"+(this.targetIndex+1)+"开始*************************************");
            logger.info("该任务url为："+this.dbUtil.getTargets().get(this.targetIndex).getUrl());

            long start_s = System.currentTimeMillis();
            this.synchronizeTable();
            logger.info("同步完成，进行下一张表的同步任务......\n");
            long end = System.currentTimeMillis();
            logger.info("已完成全部表的同步任务，共耗时 "+((end-start_s)/1000.0)+" 秒\n");

            long start = System.currentTimeMillis();
            this.synchronizeViews();
            end = System.currentTimeMillis();
            logger.info("已完成全部视图的同步任务，共耗时 "+((end-start)/1000.0)+" 秒\n");

            start = System.currentTimeMillis();
            this.synchronizeProc();
            end = System.currentTimeMillis();
            logger.info("已完成全部过程或函数的同步任务，共耗时 "+((end-start)/1000.0)+" 秒\n");
            logger.info("已完成全部同步任务，共耗时 "+((end-start_s)/1000.0)+" 秒\n");
            logger.info("*************************************同步操作任务"+(this.targetIndex+1)+"结束*************************************\n");
            if (this.dbUtil.getTargets().get(this.targetIndex).getPeriod()!=0){
                logger.info("等待下一次任务执行......");
            }
        }catch (Exception e){
            throw e;
        }finally {
            this.dbUtil.closeDB();
            this.dbUtil.closeTarget();
        }
    }

    //**************************************************数据表-start**************************************************

    /**
     * 根据表名称获取建表语句
     * @param tableName 表名称
     * @return 返回sql语句
     * @throws DbsyncException
     */
    private String getCreateTableSQL(String tableName) throws DbsyncException{
        PreparedStatement preparedStatement = null;
        ResultSet result = null;
        StringBuilder createSql = new StringBuilder();
        try {
            Connection connection = this.dbUtil.getDBConnection();
            preparedStatement = connection.prepareStatement(DBAdapter.getQueryCreateTableSQL(this.dbUtil, tableName));
            result = preparedStatement.executeQuery();
            result.next();
            createSql.append(DBAdapter.getCreateTableSQL(this.dbUtil, this.targetIndex, tableName, result.getString("Create Table")));
        }catch (Exception e){
            throw new RuntimeException(e);
        }finally {
            try {
                if (result != null){
                    result.close();
                }
                if (preparedStatement != null){
                    preparedStatement.close();
                }
            }catch (Exception e){
                throw new RuntimeException(e);
            }
        }
        return createSql.toString();
    }

    /**
     * 获取同步表
     * @return 返回同步表名称List集合
     */
    private List<String> getTables(){
        long start = System.currentTimeMillis();
        logger.info("开始执行表名称查询操作，请稍候......");
        List<String> tables = this.dbUtil.getTargets().get(this.targetIndex).getTables();
        if (tables.size()==1&&"allTables".equalsIgnoreCase(tables.get(0))){
            List<String> allTables = new ArrayList<String>();
            PreparedStatement preparedStatement = null;
            ResultSet result = null;
            try {
                Connection connection = this.dbUtil.getDBConnection();
                preparedStatement = connection.prepareStatement(DBAdapter.getQueryAllTableSQL(this.dbUtil));
                result = preparedStatement.executeQuery();
                while (result.next()){
                    allTables.add(result.getString(1));
                }
            }catch (Exception e){
                this.showErrorMsg("表名称查询", e, start);
            }finally {
                try {
                    if (result != null){
                        result.close();
                    }
                    if (preparedStatement != null){
                        preparedStatement.close();
                    }
                }catch (Exception e){
                    this.showErrorMsg("表名称查询", e, start);
                }
            }
            long end = System.currentTimeMillis();
            logger.info("执行表名称查询操作： 成功，耗时 "+((end-start)/1000.0)+" 秒\n");
            return allTables;
        }else{
            long end = System.currentTimeMillis();
            logger.info("执行表名称查询操作： 成功，耗时 "+((end-start)/1000.0)+" 秒\n");
            return tables;
        }
    }

    /**
     * 根据表名获取源数据库对应表数据
     * @param tableName 表名
     * @return 返回对应数据List集合
     */
    private List<Map<String, Object>> getTableContent(String tableName){
        long start = System.currentTimeMillis();
        logger.info("开始执行表("+tableName+")数据查询操作，请稍候......");
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        PreparedStatement preparedStatement = null;
        ResultSet result = null;
        try {
            Connection connection = this.dbUtil.getDBConnection();
            StringBuilder query = new StringBuilder();
            query.append("SELECT * FROM ").append(tableName);
            preparedStatement = connection.prepareStatement(query.toString());
            result = preparedStatement.executeQuery();
            ResultSetMetaData metaData = result.getMetaData();
            result.last();
            double count = result.getRow();
            result.beforeFirst();
            int percent = 1;
            int index = 1;
            while (result.next()){
                Map<String, Object> data = new HashMap<String, Object>();
                for (int i=1;i<=metaData.getColumnCount();i++){
                    data.put(metaData.getColumnName(i), result.getObject(i));
                }
                list.add(data);
                if (index/count*100>=percent){
                    logger.info("已读取"+(Double.valueOf(index/count*100).intValue())+"%，请稍后......");
                    percent++;
                }
                index++;
            }
        }catch (Exception e){
            this.showErrorMsg("表("+tableName+")数据查询", e, start);
        }finally {
            try {
                if (result != null){
                    result.close();
                }
                if (preparedStatement != null){
                    preparedStatement.close();
                }
            }catch (Exception e){
                this.showErrorMsg("表("+tableName+")数据查询", e, start);
            }
        }
        long end = System.currentTimeMillis();
        logger.info("执行表("+tableName+")数据查询操作： 成功，耗时 "+((end-start)/1000.0)+" 秒\n");
        return list;
    }

    /**
     * 根据表名清理任务数据库对应表数据
     * @param tableName 表名
     */
    private void clearTargetTableData(String tableName){
        long start = System.currentTimeMillis();
        logger.info("开始执行清空表("+tableName+")操作，请稍候......");
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try{
            StringBuilder delete = new StringBuilder();
            delete.append(DBAdapter.getClearTableSQL(this.dbUtil, this.targetIndex, tableName));
            StringBuilder create = new StringBuilder();
            create.append("");
            connection = this.dbUtil.getTargetConnection(this.targetIndex);
            connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement("");
            preparedStatement.addBatch(delete.toString());
            preparedStatement.addBatch(this.getCreateTableSQL(tableName));
            preparedStatement.executeBatch();
            connection.commit();
            long end = System.currentTimeMillis();
            logger.info("执行清空表("+tableName+")操作： 成功，耗时 "+((end-start)/1000.0)+" 秒\n");
        }catch (Exception e){
            try {
                if (connection != null){
                    connection.rollback();
                    connection.commit();
                }
            }catch (Exception e1){
                this.showErrorMsg("清空表("+tableName+")", e1, start);
            }
            this.showErrorMsg("清空表("+tableName+")", e, start);
        }finally {
            try {
                if (preparedStatement!=null){
                    preparedStatement.close();
                }
            }catch (Exception e){
                this.showErrorMsg("清空表("+tableName+")", e, start);
            }
        }
    }

    /**
     * 根据表名添加任务数据库对应表数据
     * @param tableName 表名
     * @return 返回boolean类型
     * @exception
     */
    private boolean addTargetTableData(String tableName) throws Exception{
        List<Map<String, Object>> dataList = this.getTableContent(tableName);
        if (dataList.isEmpty()){
            logger.info("执行同步表("+tableName+")操作： 成功，该表数据为空，跳过\n");
            return true;
        }
        long start = System.currentTimeMillis();
        logger.info("开始执行同步表("+tableName+")操作： ");
        DbsyncUncaughtExceptionHandler exceptionHandler = new DbsyncUncaughtExceptionHandler();
        Thread.setDefaultUncaughtExceptionHandler(exceptionHandler);
        ExecutorService exec = Executors.newCachedThreadPool();
        if (dataList.size() > 1000){
            int count = dataList.size()/4+1;
            for (int i=1;i<=4;i++){
                int counts = count*i;
                exec.execute(new DataInsertThead(i, this.targetIndex, this.dbUtil, dataList.subList((i-1)*count, counts<dataList.size()?counts:dataList.size()), tableName, this.logger));
            }
        }else{
            exec.execute(new DataInsertThead(1, this.targetIndex, this.dbUtil, dataList, tableName, this.logger));
        }
        exec.shutdown();
        exec.awaitTermination(1, TimeUnit.HOURS);
        long end = System.currentTimeMillis();
        if (exceptionHandler.isError()){
            logger.error("执行同步表("+tableName+")操作： 失败，失败原因为："+exceptionHandler.getReason()+"，耗时 " + ((end - start) / 1000.0) + " 秒\n");
            return false;
        }else{
            logger.info("执行同步表("+tableName+")操作： 成功，耗时 "+((end-start)/1000.0)+" 秒\n");
            return true;
        }
    }

    /**
     * 执行同步数据表任务
     * @throws Exception
     */
    private void synchronizeTable() throws Exception{
        List<String> tables = this.getTables();
        for (String table : tables) {
            this.clearTargetTableData(table);
            boolean isNotError = this.addTargetTableData(table);
            if (!isNotError) {
                if (!this.dbUtil.getTargets().get(this.targetIndex).getErrorContinue()) {
                    throw new RuntimeException("同步任务出现错误，该任务将停止运行");
                }
            }
        }
    }

    //**************************************************数据表-end**************************************************


    //**************************************************视图-start**************************************************
    /**
     * 获取对应数据库类型所有视图sql语句
     * @return 返回sql语句
     * @throws DbsyncException
     */
    private String getAllViewsSQL() throws DbsyncException{
        // TODO 需要改进
        StringBuilder sql = new StringBuilder();
        if ("mysql".equalsIgnoreCase(this.dbUtil.getDBType(0))){
            sql.append("SELECT TABLE_NAME AS name,")
                .append("VIEW_DEFINITION AS define ")
                .append("FROM information_schema.VIEWS ")
                .append("WHERE TABLE_SCHEMA='").append(this.dbUtil.getDBName()).append("'");
        }else{
            throw new DbsyncException("未支持的数据库类型");
        }
        return sql.toString();
    }

    /**
     * 获取同步视图
     * @return 返回同步视图名称及视图定义List集合
     */
    private List<Map<String, String>> getVies(){
        List<Map<String, String>> allViews = new ArrayList<Map<String, String>>();
        long start = System.currentTimeMillis();
        logger.info("开始执行视图名称及定义查询操作，请稍候......");
        PreparedStatement preparedStatement = null;
        ResultSet result = null;
        try {
            Connection connection = this.dbUtil.getDBConnection();
            preparedStatement = connection.prepareStatement(this.getAllViewsSQL());
            result = preparedStatement.executeQuery();
            while (result.next()){
                Map<String, String> map = new HashMap<String, String>();
                map.put("name", result.getString(1));
                map.put("define", result.getString(2));
                allViews.add(map);
            }
        }catch (Exception e){
            this.showErrorMsg("视图名称及定义查询", e, start);
        }finally {
            try {
                if (result != null){
                    result.close();
                }
                if (preparedStatement != null){
                    preparedStatement.close();
                }
            }catch (Exception e){
                this.showErrorMsg("视图名称及定义查询", e, start);
            }
        }
        long end = System.currentTimeMillis();
        logger.info("执行视图名称及定义查询操作： 成功，耗时 "+((end-start)/1000.0)+" 秒\n");
        return allViews;
    }

    /**
     * 根据视图集合清理任务数据库对应视图
     * @param allViews 视图集合
     */
    private void clearTargetViews(List<Map<String, String>> allViews){
        long start = System.currentTimeMillis();
        logger.info("开始执行清空视图操作，请稍候......");
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try{
            connection = this.dbUtil.getTargetConnection(this.targetIndex);
            connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement("");
            for (Map<String, String> map : allViews){
                StringBuilder delete = new StringBuilder();
                // TODO 需要改进
                delete.append("DROP VIEW IF EXISTS `").append(this.dbUtil.getTargetName(this.targetIndex)).append("`.`").append(map.get("name")).append("`");
                preparedStatement.addBatch(delete.toString());
            }
            preparedStatement.executeBatch();
            connection.commit();
            long end = System.currentTimeMillis();
            logger.info("执行清空视图操作： 成功，耗时 "+((end-start)/1000.0)+" 秒\n");
        }catch (Exception e){
            try {
                if (connection != null){
                    connection.rollback();
                    connection.commit();
                }
            }catch (Exception e1){
                this.showErrorMsg("清空视图", e1, start);
            }
            this.showErrorMsg("清空视图", e, start);
        }finally {
            try {
                if (preparedStatement!=null){
                    preparedStatement.close();
                }
            }catch (Exception e){
                this.showErrorMsg("清空视图", e, start);
            }
        }
    }

    /**
     * 根据视图集合创建任务数据库对应视图
     * @param allViews 视图集合
     */
    private void createTargetViews(List<Map<String, String>> allViews){
        long start = System.currentTimeMillis();
        logger.info("开始执行同步视图操作： ");
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try{
            connection = this.dbUtil.getTargetConnection(this.targetIndex);
            connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement("");
            for (Map<String, String> map : allViews){
                StringBuilder create = new StringBuilder();
                create.append("CREATE VIEW ").append(map.get("name")).append(" AS ").append(map.get("define"));
                preparedStatement.addBatch(create.toString());
            }
            preparedStatement.executeBatch();
            connection.commit();
            long end = System.currentTimeMillis();
            logger.info("执行同步视图操作： 成功，耗时 "+((end-start)/1000.0)+" 秒\n");
        }catch (Exception e){
            try {
                if (connection != null){
                    connection.rollback();
                    connection.commit();
                }
            }catch (Exception e1){
                this.showErrorMsg("同步视图", e1, start);
            }
            this.showErrorMsg("同步视图", e, start);
        }finally {
            try {
                if (preparedStatement!=null){
                    preparedStatement.close();
                }
            }catch (Exception e){
                this.showErrorMsg("同步视图", e, start);
            }
        }
    }

    /**
     * 执行同步视图任务
     */
    private void synchronizeViews(){
        Boolean isSyncViews = this.dbUtil.getTargets().get(this.targetIndex).getSyncViews();
        if (isSyncViews){
            List<Map<String, String>> allViews = this.getVies();
            this.clearTargetViews(allViews);
            this.createTargetViews(allViews);
        }else{
            logger.info("已设置不同步视图操作，执行视图同步操作跳过\n");
        }
    }

    //**************************************************视图-end**************************************************

    //**************************************************过程或函数-start**************************************************
    /**
     * 获取对应数据库类型所有过程或函数sql语句
     * @return 返回sql语句
     * @throws DbsyncException
     */
    private String getAllProcedureSQL() throws DbsyncException{
        // TODO 需要改进
        StringBuilder sql = new StringBuilder();
        if ("mysql".equalsIgnoreCase(this.dbUtil.getDBType(0))){
            sql.append("SELECT name,type ")
                .append("FROM mysql.proc ")
                .append("WHERE db='").append(this.dbUtil.getDBName()).append("'");
        }else{
            throw new DbsyncException("未支持的数据库类型");
        }
        return sql.toString();
    }

    /**
     * 根据过程或函数名称获取定义sql语句
     * @param data 过程或函数
     * @return 返回sql语句
     * @throws DbsyncException
     */
    private String getProcedureDefineSQL(Map<String, String> data) throws DbsyncException{
        // TODO 需要改进
        StringBuilder sql = new StringBuilder();
        if ("mysql".equalsIgnoreCase(this.dbUtil.getDBType(0))){
            sql.append("SHOW CREATE ").append(data.get("type")).append(" `").append(this.dbUtil.getDBName()).append("`.`").append(data.get("name")).append("`");
        }else{
            throw new DbsyncException("未支持的数据库类型");
        }
        return sql.toString();
    }

    /**
     * 存放过程或函数定义sql语句
     * @param data 过程或函数
     * @param result java数据集对象
     * @throws Exception
     */
    private void putProcedureDefineSQL(Map<String, String> data, ResultSet result) throws Exception{
        // TODO 需要改进
        if ("mysql".equalsIgnoreCase(this.dbUtil.getDBType(0))){
            if ("function".equalsIgnoreCase(data.get("type"))){
                while (result.next()){
                    data.put("define", result.getString("Create Function").replaceAll("DEFINER = `\\w+`@`(%|\\w+)`", ""));
                }
            }else{
                while (result.next()){
                    data.put("define", result.getString("Create Procedure").replaceAll("DEFINER = `\\w+`@`(%|\\w+)`", ""));
                }
            }
        }else{
            throw new DbsyncException("未支持的数据库类型");
        }
    }

    /**
     * 获取同步过程或函数
     * @return 返回同步过程或函数名称及定义List集合
     */
    private List<Map<String, String>> getProc(){
        List<Map<String, String>> allProc = new ArrayList<>();
        long start = System.currentTimeMillis();
        logger.info("开始执行过程或函数名称及定义查询操作，请稍候......");
        PreparedStatement preparedStatement = null;
        try {
            Connection connection = this.dbUtil.getDBConnection();
            preparedStatement = connection.prepareStatement(this.getAllProcedureSQL());
            ResultSet result = preparedStatement.executeQuery();
            while (result.next()){
                Map<String, String> map = new HashMap<>();
                map.put("name", result.getString(1));
                map.put("type", result.getString(2));
                allProc.add(map);
            }
            result.close();
            for (Map<String, String> data : allProc){
                result = preparedStatement.executeQuery(this.getProcedureDefineSQL(data));
                this.putProcedureDefineSQL(data, result);
                result.close();
            }
        }catch (Exception e){
            this.showErrorMsg("过程或函数名称及定义查询", e, start);
        }finally {
            try {
                if (preparedStatement != null){
                    preparedStatement.close();
                }
            }catch (Exception e){
                this.showErrorMsg("过程或函数名称及定义查询", e, start);
            }
        }
        long end = System.currentTimeMillis();
        logger.info("执行过程或函数名称及定义查询操作： 成功，耗时 "+((end-start)/1000.0)+" 秒\n");
        return allProc;
    }

    /**
     * 根据过程或函数集合清理任务数据库对应过程或函数
     * @param allProc 过程或函数集合
     */
    private void clearTargetProc(List<Map<String, String>> allProc){
        long start = System.currentTimeMillis();
        logger.info("开始执行清空过程或函数操作，请稍候......");
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try{
            connection = this.dbUtil.getTargetConnection(this.targetIndex);
            connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement("");
            for (Map<String, String> map : allProc){
                StringBuilder delete = new StringBuilder();
                // TODO 需要改进
                delete.append("DROP ").append(map.get("type")).append(" IF EXISTS `").append(this.dbUtil.getTargetName(this.targetIndex)).append("`.`").append(map.get("name")).append("`");
                preparedStatement.addBatch(delete.toString());
            }
            preparedStatement.executeBatch();
            connection.commit();
            long end = System.currentTimeMillis();
            logger.info("执行清空过程或函数操作： 成功，耗时 "+((end-start)/1000.0)+" 秒\n");
        }catch (Exception e){
            try {
                if (connection != null){
                    connection.rollback();
                    connection.commit();
                }
            }catch (Exception e1){
                this.showErrorMsg("清空过程或函数", e1, start);
            }
            this.showErrorMsg("清空过程或函数", e, start);
        }finally {
            try {
                if (preparedStatement!=null){
                    preparedStatement.close();
                }
            }catch (Exception e){
                this.showErrorMsg("清空过程或函数", e, start);
            }
        }
    }

    /**
     * 根据过程或函数集合创建任务数据库对应过程或函数
     * @param allProc 过程或函数集合
     */
    private void createTargetProc(List<Map<String, String>> allProc){
        long start = System.currentTimeMillis();
        logger.info("开始执行同步过程或函数操作： ");
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try{
            connection = this.dbUtil.getTargetConnection(this.targetIndex);
            connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement("");
            for (Map<String, String> map : allProc){
                preparedStatement.addBatch(map.get("define"));
            }
            preparedStatement.executeBatch();
            connection.commit();
            long end = System.currentTimeMillis();
            logger.info("执行同步过程或函数操作： 成功，耗时 "+((end-start)/1000.0)+" 秒\n");
        }catch (Exception e){
            try {
                if (connection != null){
                    connection.rollback();
                    connection.commit();
                }
            }catch (Exception e1){
                this.showErrorMsg("同步过程或函数", e1, start);
            }
            this.showErrorMsg("同步过程或函数", e, start);
        }finally {
            try {
                if (preparedStatement!=null){
                    preparedStatement.close();
                }
            }catch (Exception e){
                this.showErrorMsg("同步过程或函数", e, start);
            }
        }
    }

    /**
     * 根据任务索引执行同步过程或函数任务
     */
    private void synchronizeProc(){
        Boolean isSyncProc = this.dbUtil.getTargets().get(this.targetIndex).getSyncProc();
        if (isSyncProc){
            List<Map<String, String>> allProc = this.getProc();
            this.clearTargetProc(allProc);
            this.createTargetProc(allProc);
        }else{
            logger.info("已设置不同步过程或函数操作，执行过程或函数同步操作跳过\n");
        }
    }

    //**************************************************过程或函数-end**************************************************

    /**
     * 操作错误提示
     * @param name 操作名称
     * @param e 异常对象
     * @param startTime 操作起始时间
     */
    private void showErrorMsg(String name, Exception e, long startTime){
        long endTime = System.currentTimeMillis();
        logger.error("执行"+name+"操作： 失败，失败原因为："+e.getMessage()+"，耗时 "+((endTime-startTime)/1000.0)+" 秒\n");
        if (!this.dbUtil.getTargets().get(this.targetIndex-1).getErrorContinue()){
            throw new RuntimeException("同步任务出现错误，该任务将停止运行");
        }
    }
}
